package com.ruoyi.celoan.mapper.statistics;

import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.celoan.entity.ExTradeEffectCreditDetailEntity;
import com.ruoyi.celoan.entity.statistics.ExTradeEffectCreditStatisticsOrgEntity;
import org.apache.ibatis.annotations.Select;

public interface ExTradeEffectCreditStatisticsOrgMapper extends BaseMapper<ExTradeEffectCreditStatisticsOrgEntity> {
    ExTradeEffectCreditDetailEntity getDataReport();

    @Select({
            "SELECT count( DISTINCT m.enterprise_id ) as count, COALESCE(SUM(l.loan_amount), 0) as loanAmount",
            "FROM oc_financing_match m",
            "INNER JOIN oc_business_financing_order o ON o.mach_id = m.mach_id",
            "INNER JOIN oc_order_loan l ON l.order_no = o.order_no",
            "WHERE l.loan_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            "  AND l.loan_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')"
    })
    JSONObject countDistinctEnterpriseInCurrentMonth();


    @Select({
            "SELECT",
            "  COUNT(DISTINCT m.enterprise_id) AS count,",
            "  COALESCE(SUM(l.credit_amount), 0) AS creditAmount",
            "FROM oc_financing_match m",
            "INNER JOIN oc_business_financing_order o ON o.mach_id = m.mach_id",
            "INNER JOIN oc_order_credit l ON l.order_no = o.order_no",
            "WHERE l.credit_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            "  AND l.credit_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')"
    })
    JSONObject countDistinctEnterpriseWithCreditInCurrentMonth();


    /**
     * 查询当月企业融资申请总数及申请金额总和
     */
    @Select({
            "SELECT",
            "  COUNT(1) AS count,",
            "  COALESCE(SUM(apply_amount_max), 0) AS applyAmount",
            "FROM ec_enterprise_financing_info",
            "WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            "  AND create_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')"
    })
    JSONObject getMonthlyFinancingApplySummary();


    @Select({
            "SELECT",
            "  COUNT(1) AS count,",
            "  COALESCE(ROUND(AVG(loan_amount), 2), 0) AS loanAmount,",
            "  COALESCE(ROUND(AVG(loan_rate), 2), 0) AS loanRateAvg",
            "FROM oc_order_loan",
            "WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            "  AND create_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')"
    })
    JSONObject getMonthlyLoanAvgSummary();


    @Select({
            "SELECT",
            "  COUNT(1) AS count,",
            "  COALESCE(ROUND(AVG(credit_amount), 2), 0) AS creditAmount,",
            "  COALESCE(ROUND(AVG(real_rate), 2), 0) AS creditRateAvg",
            "FROM oc_order_credit",
            "WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            "  AND create_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')"
    })
    JSONObject getMonthlyCreditAvgSummary();




}


/* Location:              D:\tuodayun\workspace\work\celoan-third-data-report-service-2.0.0-SNAPSHOT.jar!\BOOT-INF\classes\com\celoan\mapper\statistics\ExTradeEffectCreditStatisticsOrgMapper.class
 * Java compiler version: 8 (52.0)
 * JD-Core Version:       1.1.3
 */
